/* $Id: SingleClientCompanyDAOImpl.java 
 * Copyright (C) 2011, TRAKHEES
 *  
 * Revision History
 *
 * Date  			Author 		    	Remarks
 * ---------------------------------------------------------------------------------
 *
 */
package ae.pcfc.etks.online.common.dao.provider;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.StructDescriptor;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.jboss.resource.adapter.jdbc.WrappedConnection;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

import ae.pcfc.etks.online.common.ConstantsCommon;
import ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO;
import ae.pcfc.etks.online.common.domain.ClientCompanyReq;
import ae.pcfc.etks.online.common.domain.ClientCompanyRoleReq;
import ae.pcfc.etks.online.common.domain.TTscClientCategory;
import ae.pcfc.etks.online.common.domain.TTscClientCompany;
import ae.pcfc.etks.online.common.domain.TTscLicType;
import ae.pcfc.etks.online.common.exception.SystemException;
import ae.pcfc.etks.online.common.exception.TscDuplicateClientEmailException;
import ae.pcfc.etks.online.common.vo.ActivityVO;
import ae.pcfc.etks.online.common.vo.ClientNameVO;
import ae.pcfc.etks.online.common.vo.LicenseVO;
import ae.pcfc.etks.online.common.vo.NonClientCompanyVO;
import ae.pcfc.etks.online.common.vo.SearchCriteriaActivityVO;
import ae.pcfc.etks.online.common.vo.SearchCriteriaCompanyVO;
import ae.pcfc.etks.online.common.vo.TrakheesIdAndPINVO;

@Repository
public class SingleClientCompanyDAOImpl extends SingleClientBaseDAO implements SingleClientCompanyDAO {

	/**
	 * add
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#add(ae.pcfc.etks.online.common.domain.ClientCompanyReq)
	 * 
	 */
	public String add(ClientCompanyReq companyReq) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_COMPANY_INSERT_PROC);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(
				new SqlParameter("P_TSC_PARTNER_SYSTEM_ID", Types.VARCHAR), 
				new SqlParameter("P_CC_NAME", Types.VARCHAR), 
				new SqlParameter("P_CC_NAME_ARB", Types.VARCHAR),
				new SqlParameter("P_CC_OP_NAME", Types.VARCHAR),
				new SqlParameter("P_CC_OP_NAME_ARB", Types.VARCHAR),
				new SqlParameter("P_CC_CATEGORY", Types.VARCHAR),
				new SqlParameter("P_CC_LIC_ISSUING_AUTHORITY", Types.VARCHAR),
				new SqlParameter("P_CC_LIC_TYPE", Types.VARCHAR),
				new SqlParameter("P_CC_LIC_NO", Types.VARCHAR),
				new SqlParameter("P_CC_LIC_STATUS", Types.VARCHAR),
				new SqlParameter("P_CC_LIC_ISSUE_DATE", Types.DATE),
				new SqlParameter("P_CC_LIC_EXPIRY_DATE", Types.DATE),
				new SqlParameter("P_CC_LIC_CANCEL_DATE", Types.DATE),
				new SqlParameter("P_CC_POBOX", Types.VARCHAR),
				new SqlParameter("P_CC_POBOX_LOCATION", Types.VARCHAR),
				new SqlParameter("P_CC_ADDRESS", Types.VARCHAR),
				new SqlParameter("P_CC_ADDRESS_ARB", Types.VARCHAR),
				new SqlParameter("P_CC_OFF_TEL", Types.VARCHAR),
				new SqlParameter("P_CC_OFF_FAX", Types.VARCHAR),
				new SqlParameter("P_CC_EMAIL", Types.VARCHAR),
				new SqlParameter("P_CC_WEBSITE", Types.VARCHAR),
				new SqlParameter("P_CC_STREET_NO", Types.VARCHAR),
				new SqlParameter("P_CC_BUILDING_NO", Types.VARCHAR),
				new SqlParameter("P_CC_FLOOR_NO", Types.VARCHAR),
				new SqlParameter("P_CC_UNIT_NO", Types.VARCHAR),
				new SqlParameter("P_CC_UNIT_TYPE", Types.VARCHAR),
				new SqlParameter("P_CC_CREATEDUSER", Types.VARCHAR),
				new SqlParameter("P_CC_CREATEDTIME", Types.DATE),
				new SqlParameter("P_CC_MODIFIEDUSER", Types.VARCHAR),
				new SqlParameter("P_CC_MODIFIEDTIME", Types.DATE),
				new SqlParameter("P_CC_STATUS", Types.VARCHAR),
				new SqlParameter("P_CC_LIC_ID", Types.VARCHAR),
				new SqlParameter("P_CC_CITY", Types.VARCHAR), 
				new SqlParameter("P_CC_CITY_ARB", Types.VARCHAR), 
				new SqlParameter("P_CC_COUNTRY", Types.VARCHAR), 
				new SqlParameter("P_CC_COUNTRY_ARB", Types.VARCHAR), 
				new SqlParameter("P_CC_LEGAL_TYPE", OracleTypes.NUMBER),
				new SqlParameter("P_SYS_ATTRIBUTES", Types.ARRAY), 
				new SqlParameter("P_CLIENT_ROLES", OracleTypes.ARRAY), 
				new SqlParameter("P_CLIENT_RELATIONSHIP", Types.ARRAY),
				new SqlOutParameter("P_CC_TRAKHEES_ID", Types.VARCHAR));

		StructDescriptor structDescriptor=null;
		ArrayDescriptor arrayDescriptor=null;
		
		Object[][] recObj1 = null, recObj2 = null, recObj3 = null;
		ARRAY arr1 = null, arr2=null, arr3= null;
		Connection conn = null;
		try{
			Connection jbossConn = getJdbcTemplate().getDataSource().getConnection();
			conn = ((WrappedConnection) jbossConn).getUnderlyingConnection(); 
			structDescriptor = StructDescriptor.createDescriptor(ConstantsCommon.SINGLE_CLIENT_CLIENT_ROLE_SQL_TYPE, conn);
			arrayDescriptor = ArrayDescriptor.createDescriptor(ConstantsCommon.SINGLE_CLIENT_CLIENT_ROLES_SQL_TYPE, conn);
			
			if(companyReq.getClientCompanyRoleReqs() != null){
				recObj2 = new Object[companyReq.getClientCompanyRoleReqs().size()][2];
				Iterator<ClientCompanyRoleReq> iterator = companyReq.getClientCompanyRoleReqs().iterator();
				int i = 0;
				while(iterator.hasNext()){
					ClientCompanyRoleReq req = iterator.next();
					recObj2[i][0] = String.valueOf(req.getCsrReqId());
					recObj2[i][1] = ConstantsCommon.ACTIVE;
					i++;
				}

				
				arr2 = new ARRAY(arrayDescriptor, conn, recObj2);
				recObj1 = new Object[0][3];
				recObj3 = new Object[0][6];
				arr1 = new ARRAY(ArrayDescriptor.createDescriptor(ConstantsCommon.SINGLE_CLIENT_SYS_ATTRS_SQL_TYPE, conn), conn, recObj1);
				arr3 = new ARRAY(ArrayDescriptor.createDescriptor(ConstantsCommon.SINGLE_CLIENT_CLIENT_RELATIONSHIPS_SQL_TYPE, conn), conn, recObj3);
			}
		

		SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_TSC_PARTNER_SYSTEM_ID", ConstantsCommon.PARTNER_SYSTEM_ID)
				.addValue("P_CC_NAME", companyReq.getCcNameEng())
				.addValue("P_CC_NAME_ARB", companyReq.getCcNameArb())
				.addValue("P_CC_OP_NAME", companyReq.getCcNameEng())
				.addValue("P_CC_OP_NAME_ARB", companyReq.getCcNameArb())
				.addValue("P_CC_CATEGORY", companyReq.getCcCategory())
				.addValue("P_CC_LIC_ISSUING_AUTHORITY", companyReq.getCcLicIssuingAuthority())
				.addValue("P_CC_LIC_TYPE", companyReq.getCcLicType())
				.addValue("P_CC_LIC_NO", companyReq.getCcLicNo())
				.addValue("P_CC_LIC_STATUS", companyReq.getCcLicStatus())
				.addValue("P_CC_LIC_ISSUE_DATE", companyReq.getCcLicIssueDate())
				.addValue("P_CC_LIC_EXPIRY_DATE", companyReq.getCcLicExpiryDate())
				.addValue("P_CC_LIC_CANCEL_DATE", Calendar.getInstance().getTime())
				.addValue("P_CC_POBOX", companyReq.getCcPoboxNo())
				.addValue("P_CC_POBOX_LOCATION", companyReq.getCcPoboxLocation())
				.addValue("P_CC_ADDRESS", companyReq.getCcAddressArb())
				.addValue("P_CC_ADDRESS_ARB", companyReq.getCcAddressArb())
				.addValue("P_CC_OFF_TEL", companyReq.getCcOffTel())
				.addValue("P_CC_OFF_FAX", companyReq.getCcOffFax())
				.addValue("P_CC_EMAIL", companyReq.getCcEmail())
				.addValue("P_CC_WEBSITE", companyReq.getCcWebsite())
				.addValue("P_CC_STREET_NO", null)
				.addValue("P_CC_BUILDING_NO", null)
				.addValue("P_CC_FLOOR_NO", null)
				.addValue("P_CC_UNIT_NO", null)
				.addValue("P_CC_UNIT_TYPE", null)
				.addValue("P_CC_CREATEDUSER", companyReq.getCpCreatedUser())
				.addValue("P_CC_CREATEDTIME", Calendar.getInstance().getTime())
				.addValue("P_CC_MODIFIEDUSER", companyReq.getCpCreatedUser())
				.addValue("P_CC_MODIFIEDTIME", Calendar.getInstance().getTime())
				.addValue("P_CC_STATUS", "A")
				.addValue("P_CC_LIC_ID", null)
				.addValue("P_CC_CITY", companyReq.getCcCity())
				.addValue("P_CC_CITY_ARB", "0")
				.addValue("P_CC_LEGAL_TYPE", null)
				.addValue("P_CC_COUNTRY", companyReq.getCcCountry())
				.addValue("P_CC_COUNTRY_ARB", "0")
				.addValue("P_SYS_ATTRIBUTES", arr1)
				.addValue("P_CLIENT_ROLES", arr2)
				.addValue("P_CLIENT_RELATIONSHIP", arr3);
				
		
		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		
		return result.get("P_CC_TRAKHEES_ID").toString();
		} catch(Exception e){
			throw new SystemException(e);
		}
	}

	/**
	 * findSCCompanyByTrakheesId
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#findSCCompanyByTrakheesId(java.lang.String)
	 * 
	 */
	public TTscClientCompany findSCCompanyByTrakheesId(String trakheesId) {
		@SuppressWarnings("deprecation")
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName(
				ConstantsCommon.GET_SINGLE_CLIENT_COMPANY_BY_TRAKHEES_ID).returningResultSet("P_RECORDSET",
				ParameterizedBeanPropertyRowMapper.newInstance(TTscClientCompany.class));

		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_TRAKHEES_ID", Types.VARCHAR));
		SqlParameterSource in = new MapSqlParameterSource().addValue("P_TRAKHEES_ID", trakheesId);
		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		@SuppressWarnings("unchecked")
		List<TTscClientCompany> resultObj = (List<TTscClientCompany>) result.get("P_RECORDSET");

		if (resultObj != null && resultObj.size() > 0) {
			return resultObj.get(0);
		} else
			return null;
	}

	/**
	 * getPinCodeByTrakheesId
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#getPinCodeByTrakheesId(java.lang.String)
	 * 
	 */
	public String getPinCodeByTrakheesId(String trakheesId) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.GET_CLIENT_PIN_BY_TRAKHEES_ID);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_TRAKHEES_ID", Types.VARCHAR), new SqlOutParameter("P_PIN_CODE",
				Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue("P_TRAKHEES_ID", trakheesId);

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);

		return result.get("P_PIN_CODE").toString();
	}

	/**
	 * findTrkheesIdAndPinByEmail
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#findTrkheesIdAndPinByEmail(java.lang.String)
	 * 
	 */
	public TrakheesIdAndPINVO findTrkheesIdAndPinByEmail(final String email) {

		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.GET_TAKHEES_ID_AND_PIN_BY_EMAIL).returningResultSet("P_RECORDSET", new RowMapper<TrakheesIdAndPINVO>() {
					public TrakheesIdAndPINVO mapRow(ResultSet rs, int arg1)
							throws SQLException {
						// TODO Auto-generated method stub
						TrakheesIdAndPINVO idPin = new TrakheesIdAndPINVO();
						idPin.setStrTrakheesID(rs.getString("P_TRAKHEES_ID"));
						idPin.setStrTrakheesPIN(rs.getString("P_PIN_CODE"));
						idPin.setStrEmail(email);
						return idPin;
					}
				});
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

/*		jdbcCall = jdbcCall.declareParameters(new SqlOutParameter("P_TRAKHEES_ID", Types.VARCHAR), new SqlOutParameter(
				"P_PIN_CODE", Types.VARCHAR), new SqlParameter("P_CC_EMAIL", Types.VARCHAR));
*/
		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_CC_EMAIL", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue("P_CC_EMAIL", email);

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		@SuppressWarnings("unchecked")
		List<TrakheesIdAndPINVO> resultObj = (List<TrakheesIdAndPINVO>) result.get("P_RECORDSET");

/*		if (result.get("P_TRAKHEES_ID") != null && result.get("P_PIN_CODE") != null) {
		TrakheesIdAndPINVO idAndPINVO = new TrakheesIdAndPINVO();

		idAndPINVO.setStrTrakheesID(result.get("P_TRAKHEES_ID").toString());
		idAndPINVO.setStrTrakheesPIN(result.get("P_PIN_CODE").toString());

		return idAndPINVO;
	} else
		return null;
*/
		if (resultObj != null && resultObj.size() > 1) {
			throw new TscDuplicateClientEmailException(email);
		}
		else if (resultObj != null && resultObj.size() == 1) {
			return resultObj.get(0);
		}else{
			return null;
		}

}

	/**
	 * validateLicense
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#validateLicense(ae.pcfc.etks.online.common.vo.LicenseVO)
	 * 
	 */
	public boolean validateLicense(LicenseVO licenseVO) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_VALIDATE_LICENSE);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_CC_LIC_NO", Types.VARCHAR), new SqlParameter(
				"P_CC_LIC_ISSUING_AUTHORITY", Types.VARCHAR), new SqlOutParameter("P_VALID", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue("P_CC_LIC_NO", licenseVO.getLicenseNo()).addValue(
				"P_CC_LIC_ISSUING_AUTHORITY", licenseVO.getLicenseIssuingAuthorityCode());

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);

		String string = (String) result.get("P_VALID");

		if (string.equals(ConstantsCommon.LICENSE_VALID))
			return true;
		else
			return false;
	}

	/**
	 * findSCCompanyByVO
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#findSCCompanyByVO(ae.pcfc.etks.online.common.vo.NonClientCompanyVO)
	 * 
	 */
	public List<TTscClientCompany> findSCCompanyByVO(NonClientCompanyVO nonClientCompanyVO) {
		@SuppressWarnings("deprecation")
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_COMPANY_SEARCH).returningResultSet("P_RECORDSET",
						ParameterizedBeanPropertyRowMapper.newInstance(TTscClientCompany.class));
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_CC_LIC_NO", Types.VARCHAR), new SqlParameter("P_CC_CLIENTID",
				Types.NUMERIC), new SqlParameter("P_CC_NAME", Types.VARCHAR), new SqlParameter("P_CC_NAME_ARB", Types.VARCHAR),
				new SqlParameter("P_CC_OP_NAME", Types.VARCHAR), new SqlParameter("P_CC_OP_NAME_ARB", Types.VARCHAR),
				new SqlParameter("P_CC_LIC_ISSUING_AUTHORITY", Types.VARCHAR), new SqlParameter("P_CC_OFF_TEL", Types.VARCHAR),
				new SqlParameter("P_CC_POBOX", Types.VARCHAR), new SqlParameter("P_CC_CATEGORY", Types.VARCHAR));
		SqlParameterSource in = new MapSqlParameterSource().addValue("P_CC_LIC_NO", nonClientCompanyVO.getStrLicenseNumber())
				.addValue("P_CC_CLIENTID", nonClientCompanyVO.getiClientId())
				.addValue("P_CC_NAME", nonClientCompanyVO.getStrCompNameEng())
				.addValue("P_CC_NAME_ARB", nonClientCompanyVO.getStrCompNameArab())
				.addValue("P_CC_OP_NAME", nonClientCompanyVO.getStrCompNameEng())
				.addValue("P_CC_OP_NAME_ARB", nonClientCompanyVO.getStrCompNameArab())
				.addValue("P_CC_LIC_ISSUING_AUTHORITY", nonClientCompanyVO.getStrLicenseIssuingAuth())
				.addValue("P_CC_OFF_TEL", nonClientCompanyVO.getStrOfficeNo())
				.addValue("P_CC_POBOX", nonClientCompanyVO.getStrPObox())
				.addValue("P_CC_CATEGORY", nonClientCompanyVO.getStrCategory());

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		@SuppressWarnings("unchecked")
		List<TTscClientCompany> resultObj = (List<TTscClientCompany>) result.get("P_RECORDSET");

		if (resultObj != null && resultObj.size() > 0) {
			return resultObj;
		} else
			return null;
	}
	/**
	 * searchCompany:	Implementation of Finder for the Companies based upon the Criteria object passed to the method
	 *
	 * Description: This method is exposed as an API to vendor to fetch the companies based on the SearchCriteriaCompanyVO
	 * This method fetches the details from the TSC stored procedure.
	 * @author Dicetek.Ismail
	 * @param	SearchCriteriaCompanyVO - Contains the fields to be included in the search of the companies
	 * @return	returns a collection of TTscClientCompany domain objects based upon the search criteria
	 * 
	 */	
	/**
	 * searchCompany
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#searchCompany(ae.pcfc.etks.online.common.vo.SearchCriteriaCompanyVO)
	 * 
	 */
	public List<TTscClientCompany> searchCompany(SearchCriteriaCompanyVO searchCriteriaCompanyVO) {
		@SuppressWarnings("deprecation")
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_COMPANY_SEARCH).returningResultSet("P_RECORDSET",
						ParameterizedBeanPropertyRowMapper.newInstance(TTscClientCompany.class));
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(
				new SqlParameter("P_CC_LIC_NO", Types.VARCHAR), 
				new SqlParameter("P_CC_CLIENTID",Types.NUMERIC), 
				new SqlParameter("P_CC_NAME", Types.VARCHAR), 
				new SqlParameter("P_CC_NAME_ARB", Types.VARCHAR),
				new SqlParameter("P_CC_OP_NAME", Types.VARCHAR), 
				new SqlParameter("P_CC_OP_NAME_ARB", Types.VARCHAR),
				new SqlParameter("P_CC_LIC_ISSUING_AUTHORITY", Types.VARCHAR), 
				new SqlParameter("P_CC_OFF_TEL", Types.VARCHAR),
				new SqlParameter("P_CC_POBOX", Types.VARCHAR), 
				new SqlParameter("P_CC_CATEGORY", Types.VARCHAR),
				new SqlParameter("P_CC_CLIENTROLE", Types.VARCHAR),
				new SqlParameter("P_CC_EMAIL", Types.VARCHAR),
				new SqlParameter("P_CC_TRAKHEES_ID", Types.VARCHAR));
		SqlParameterSource in = new MapSqlParameterSource().addValue("P_CC_LIC_NO", searchCriteriaCompanyVO.getLicenseNumber())
				.addValue("P_CC_CLIENTID", searchCriteriaCompanyVO.getClientId())
				.addValue("P_CC_NAME", searchCriteriaCompanyVO.getCompanyNameEng())
				.addValue("P_CC_NAME_ARB", searchCriteriaCompanyVO.getCompanyNameArb())
				.addValue("P_CC_OP_NAME", searchCriteriaCompanyVO.getCompanyNameEng())
				.addValue("P_CC_OP_NAME_ARB", searchCriteriaCompanyVO.getCompanyNameArb())
				.addValue("P_CC_LIC_ISSUING_AUTHORITY", searchCriteriaCompanyVO.getLicenseIssuingAuth())
				.addValue("P_CC_OFF_TEL", searchCriteriaCompanyVO.getOfficeNumber())
				.addValue("P_CC_POBOX", searchCriteriaCompanyVO.getPoNumber())
				.addValue("P_CC_CATEGORY", searchCriteriaCompanyVO.getCategory())
				.addValue("P_CC_CLIENTROLE", searchCriteriaCompanyVO.getClientRoleIdListAsString())
				.addValue("P_CC_EMAIL", searchCriteriaCompanyVO.getEmail())
				.addValue("P_CC_TRAKHEES_ID", searchCriteriaCompanyVO.getTrakheesID());

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		@SuppressWarnings("unchecked")
		List<TTscClientCompany> resultObj = (List<TTscClientCompany>) result.get("P_RECORDSET");

		if (resultObj != null && resultObj.size() > 0) {
			return resultObj;
		} else
			return null;
	}	

	/**
	 * validateClientName
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#validateClientName(ae.pcfc.etks.online.common.vo.ClientNameVO)
	 * 
	 */
	public boolean validateClientName(ClientNameVO clientNameVO) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.SINGLE_CIENT_VALIDATE_CLIENT_NAME);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_CLIENT_NAME_EN", Types.VARCHAR), new SqlParameter(
				"P_CLIENT_NAME_AR", Types.VARCHAR), new SqlOutParameter("P_VALID", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue("P_CLIENT_NAME_EN", clientNameVO.getStrNameEng()).addValue(
				"P_CLIENT_NAME_AR", clientNameVO.getStrNameArb());

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);

		String string = (String) result.get("P_VALID");

		if (string.equals(ConstantsCommon.LICENSE_VALID))
			return true;
		else
			return false;
	}

	/**
	 * getClientCategory
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#getClientCategory(java.lang.String)
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<TTscClientCategory> getClientCategory(String clientType) {

		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession()
				.createCriteria(TTscClientCategory.class);

		if (clientType != null)
			criteria.add(Restrictions.eq("ctCategoryType", clientType));

		criteria.add(Restrictions.eq("ctStatus", ConstantsCommon.ACTIVE));
		return (List<TTscClientCategory>) criteria.list();
	}

	/**
	 * getClientCategoryById
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#getClientCategoryById(java.lang.String)
	 * 
	 */
	public TTscClientCategory getClientCategoryById(String categoryId) {

		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession()
				.createCriteria(TTscClientCategory.class);

		criteria.add(Restrictions.eq("ctId", categoryId));
		criteria.add(Restrictions.eq("ctStatus", ConstantsCommon.ACTIVE));
		TTscClientCategory clientCategory = (TTscClientCategory) criteria.uniqueResult();

		return clientCategory;
	}

	/**
	 * getClientLicenseTypeByCode
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#getClientLicenseTypeByCode(java.lang.String)
	 * 
	 */
	public TTscLicType getClientLicenseTypeByCode(String licenseTypeCode) {

		Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(TTscLicType.class);

		criteria.add(Restrictions.eq("ltCode", licenseTypeCode));
		criteria.add(Restrictions.eq("ltStatus", ConstantsCommon.ACTIVE));
		
		TTscLicType licType = (TTscLicType) criteria.uniqueResult();

		return licType;

	}
	/**
	 * findActivitiesOfClientCompany:	Implementation of Finder for the activities of a Company based upon the Criteria object passed to the method
	 *
	 * Description: This method is exposed as an API to vendor to fetch the activities of a company.
	 * This method fetches the details from the TSC stored procedure.
	 * @author Dicetek.Ismail
	 * @param	SearchCriteriaActivityVO - Contains the fields to be included in the search of the activities
	 * @return	returns a collection of Activity Value objects based upon the search criteria
	 * 
	 */	
	/**
	 * findActivitiesOfClientCompany
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientCompanyDAO#findActivitiesOfClientCompany(ae.pcfc.etks.online.common.vo.SearchCriteriaActivityVO)
	 * 
	 */
	public List<ActivityVO> findActivitiesOfClientCompany(SearchCriteriaActivityVO searchCriteriaActivityVO) {
		// TODO Auto-generated method stub
		if(searchCriteriaActivityVO == null || StringUtils.isBlank(searchCriteriaActivityVO.getTrakheesID())){
			return null;
		}
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_COMPANY_ACTIVITY_SEARCH).returningResultSet("P_RECORDSET",
						new RowMapper<ActivityVO>() {
							public ActivityVO mapRow(ResultSet rs, int arg1) throws SQLException {
								ActivityVO activityVO = new ActivityVO();
								activityVO.setTrakheesID(rs.getString("TRAKHEES_ID"));
								activityVO.setLicenseNumber(rs.getString("LIC_NUMBER"));
								activityVO.setLicenseIssuingAuth(rs.getString("LIC_ISSUING_AUTHORITY"));
								activityVO.setLicenseType(rs.getString("LIC_TYPE"));
								activityVO.setActivityCode(rs.getString("ACTIVITY_CODE"));
								activityVO.setActivityDescEn(rs.getString("ACTIVITY_DESC_ENG"));
								activityVO.setActivityDescAr(rs.getString("ACTIVITY_DESC_ARB"));
								activityVO.setDEDActivityGroupCode(rs.getString("DED_ACTIVITY_GROUP_CODE"));
								return activityVO;
							}
						});
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver
		jdbcCall = jdbcCall.declareParameters(
				new SqlParameter("P_ISSUE_AUTHORITY", Types.VARCHAR), 
				new SqlParameter("P_LIC_NO",Types.VARCHAR), 
				new SqlParameter("P_TRAKHEES_ID", Types.VARCHAR), 
				new SqlParameter("P_ACTIVITY_CODE", Types.VARCHAR),
				new SqlParameter("P_ACTIVITY_DESC", Types.VARCHAR) 
				);
		SqlParameterSource in = new MapSqlParameterSource().addValue("P_ISSUE_AUTHORITY", null)
				.addValue("P_LIC_NO", null)
				.addValue("P_TRAKHEES_ID", searchCriteriaActivityVO.getTrakheesID())
				.addValue("P_ACTIVITY_CODE", null)
				.addValue("P_ACTIVITY_DESC", null);

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		@SuppressWarnings("unchecked")
		List<ActivityVO> resultObj = (List<ActivityVO>) result.get("P_RECORDSET");

		if (resultObj != null && resultObj.size() > 0) {
			return resultObj;
		} else
			return null;
	}

	public String updateClientCompanyStatus(ClientCompanyReq companyReq, String strTrakheesId) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_COMPANY_UPDATE_STATUS_PROC);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver
		
		jdbcCall = jdbcCall.declareParameters(new SqlParameter(
				"P_CC_TRAKHEES_ID", Types.VARCHAR), new SqlOutParameter("P_UPDATED", Types.VARCHAR));
		
		SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_CC_TRAKHEES_ID", strTrakheesId);
		
		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		return result.get("P_UPDATED").toString();
	}

}
